package Model;
import POJO.*;

import java.io.UnsupportedEncodingException;
import java.sql.*;
import java.util.ArrayList;

public class UserModel {
	public static UserPOJO selectUserByUserNamePassword (String username, String password ) {
        Connection con = DataProvider.Connection();
        UserPOJO user = null;
        if (con != null){
            try {
                PreparedStatement ps;
                ps  = con.prepareCall("select * from [user] where username = ? and password = ?");
                ps.setString(1, username);
                ps.setString(2, password);
                ResultSet rs = ps.executeQuery();
                if(rs.next()) {
                	user = new UserPOJO();
                	user.setId(rs.getInt("id"));
                	user.setUsername(rs.getString("username"));
                	user.setPassword(rs.getString("password"));
                	user.setType(rs.getString("type"));
                }
                con.close();
            } catch (SQLException ex) {
                return null;
            }    
        }
        return user;
    }
	public static UserPOJO selectUserByID (String id ) {
        Connection con = DataProvider.Connection();
        UserPOJO user = null;
        if (con != null){
            try {
                PreparedStatement ps;
                ps  = con.prepareCall("select * from [user] where id=?");
                ps.setString(1, id);
                ResultSet rs = ps.executeQuery();
                if(rs.next()) {
                	user = new UserPOJO();
                	user.setId(rs.getInt("id"));
                	user.setUsername(rs.getString("username"));
                	user.setPassword(rs.getString("password"));
                	user.setType(rs.getString("type"));
                }
                con.close();
            } catch (SQLException ex) {
                return null;
            }    
        }
        return user;
    }
	public static ArrayList<UserPOJO> SelectUserAll()
	{
		Connection con = DataProvider.Connection();
		UserPOJO user=null;
		ArrayList<UserPOJO> listuser= new  ArrayList<UserPOJO>();
		 {
	    	  try {
	                PreparedStatement ps;
	                ps  = con.prepareCall("select * from [user]");
	                ResultSet rs = ps.executeQuery();
	                while( rs.next()) {
	                	user = new UserPOJO();
	                   	user.setId(rs.getInt("id"));
	                	user.setUsername(rs.getString("username"));
	                	user.setPassword(rs.getString("password"));
	                	user.setType(rs.getString("type"));
	                	listuser.add(user);
	                }
	                con.close();
	            } catch (SQLException ex) {
	                return null;
	            }    
	     }
	     return listuser;
	}
	public static ArrayList<UserPOJO> TimKiem(String Ser) throws UnsupportedEncodingException
	{
		Connection con = DataProvider.Connection();
		UserPOJO user=null;
		ArrayList<UserPOJO> listuser= new  ArrayList<UserPOJO>();
		if(con!=null)
		 {
	    	  try {
	                PreparedStatement ps;
	                if(Ser != "")
	                {
	               
	                ps  = con.prepareCall("select * from [user] where username Like ? ");
	                byte pname[]=Ser.getBytes("ISO-8859-1");
	                String value1= new String(pname,"UTF-8");
	                value1 = '%' + value1 + '%';
	                ps.setString(1,value1);
	                }
	                else
	                {
	                	 ps  = con.prepareCall("select * from [user] ");
	                }
	                ResultSet rs = ps.executeQuery();
	                while( rs.next()) {
	                	user = new UserPOJO();
	                   	user.setId(rs.getInt("id"));
	                	user.setUsername(rs.getString("username"));
	                	user.setPassword(rs.getString("password"));
	                	user.setType(rs.getString("type"));
	                	listuser.add(user);
	                }
	                con.close();
	            } catch (SQLException ex) {
	                return null;
	            }    
		 }
	     return listuser;
	}
	public static int InsertUser(String username,String Password,String type) throws UnsupportedEncodingException
	{
		int flag=0;
		Connection con = DataProvider.Connection();
		if(con!=null)
		{
			  try {
	                PreparedStatement ps;
	                ps  = con.prepareCall("Insert into [user] values(?,?,?)");
	                byte pname[]=username.getBytes("ISO-8859-1");
	                String value1= new String(pname,"UTF-8");
	                ps.setString(1,value1);
	                ps.setString(2,Password);
	                ps.setString(3,type);
	                flag = ps.executeUpdate();              
	                con.close();
	            } catch (SQLException ex) {
	            	ex.printStackTrace();
	            }    
		}
		return flag;
	}
	public static int DeleteUser(int id)
	{
		int flag=0;
		Connection con = DataProvider.Connection();
		if(con!=null)
		{
			  try {
	                PreparedStatement ps;
	                ps  = con.prepareCall("Delete from [user] where id=?");
	                ps.setInt(1, id);
	                flag = ps.executeUpdate();              
	                con.close();
	            } catch (SQLException ex) {
	            	ex.printStackTrace();
	            }    
		}
		return flag;
	}
	public static int UpdateUser(String username,String Password,String type,String ID) throws UnsupportedEncodingException
	{
		int flag=0;
		Connection con = DataProvider.Connection();
		if(con!=null)
		{
			  try {
	                PreparedStatement ps;
	                ps  = con.prepareCall("Update [user] set username=?,[password]=?,[type]=? where id=?");
	                byte pname[]=username.getBytes("ISO-8859-1");
	                String value1= new String(pname,"UTF-8");
	                ps.setString(1,value1);
	           //
	                ps.setString(2,Password);
	                ps.setString(3,type);
	                ps.setString(4, ID);
	                flag = ps.executeUpdate();              
	                con.close();
	            } catch (SQLException ex) {
	            	ex.printStackTrace();
	            }    
		}
		return flag;
	}
}
